MySQL 学习(0)优化的核心 explain 执行计划
在学习更深入的原理知识前,先来了解下如何查看 MySQL 的执行信息
什么是执行计划?
执行计划,就是一条 SQL 语句,在数据库中实际执行的时候,一步步的分别都做了什么。也就是我们用 EXPLAIN 分析一条 SQL 语句时展示出来的那些信息。
EXPLAIN
命令是查看查询优化器是如何决定执行查询的主要方法,从它的查询结果中可以知道一个 SQL 语句每一步是如何执行的,都经历了些什么,分为哪几步,有没有用到索引,哪些字段用到了什么样的索引,是否有一些可优化的地方等,这些信息都是我们 SQL 优化的依据。
一个例子:
基本语法:
explain select ...
一些变体
explain extended select ...
执行计划包含的信息
不同版本的 Mysql 和不同的存储引擎执行计划不完全相同,但基本信息都差不多。mysql 执行计划主要包含以下信息:
id 子查询顺序
有一组数字组成。表示一个查询中各个子查询的执行顺序;
id 相同执行顺序由上至下。
id 不同,id 值越大优先级越高,越先被执行。
id为 null 时表示一个结果集,不需要使用它查询,常出现在包含 union 等查询语句中。
select_type 查询类型
每个子查询的查询类型,一些常见的查询类型。
id | select_type | description |
---|---|---|
1 | SIMPLE | 不包含任何子查询或union等查询 |
2 | PRIMARY | 包含子查询最外层查询就显示为 PRIMARY |
3 | SUBQUERY | 在select或 where字句中包含的查询 |
4 | DERIVED | from字句中包含的查询 |
5 | UNION | 出现在union后的查询语句中 |
6 | UNION RESULT | 从UNION中获取结果集,例如上文的第三个例子 |
table 表
查询的数据表,当从衍生表中查数据时会显示 <derivedx> x
表示对应的执行计划 id。
partitions 分区
表分区、表创建的时候可以指定通过那个列进行表分区。 举个例子:
create table tmp (
id int unsigned not null AUTO_INCREMENT,
name varchar(255),
PRIMARY KEY (id)
) engine = innodb
partition by key (id) partitions 5;
type 联接类型
联接类型。下面给出各种联接类型,按照从最佳类型到最坏类型进行排序:(重点看 ref,rang,index)
ALL
Full Table Scan,遍历全表以找到匹配的行
index
遍历索引,Full Index Scan,index 与 ALL 区别为 index 类型只遍历索引树。这通常为 ALL 块,因为索引文件通常比数据文件小。(Index 与 ALL 虽然都是读全表,但 index 是从索引中读取,而 ALL 是从硬盘读取)
range
只检索给定范围的行,使用一个索引来选择行。key列显示使用了那个索引。一般就是在 where 语句中出现了 bettween、<、>、in 等的查询。这种索引列上的范围扫描比全索引扫描要好。只需要开始于某个点,结束于另一个点,不用扫描全部索引。
index_subquery
在子查询中使用 ref
unique_subquery
在子查询中使用 eq_ref
ref_or_null
对 Null 进行索引的优化的 ref
fulltext
使用全文索引
ref
非唯一性索引扫描,返回匹配某个单独值的所有行。本质是也是一种索引访问,它返回所有匹配某个单独值的行,然而他可能会找到多个符合条件的行,所以它应该属于查找和扫描的混合体。
eq_ref
唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配。常见于主键 或 唯一索引扫描。
const
表示通过索引一次就找到了,const 用于比较 primary key
或者 unique
索引。因为只需匹配一行数据,所以很快。如果将主键置于 where
列表中,mysql
就能将该查询转换为一个 const
system const
表只有一行记录(等于系统表),这是 const 类型的特例,平时不会出现,可以忽略不计
possible_keys 可能的索引
可能使用的索引,注意不一定会使用。查询涉及到的字段上若存在索引,则该索引将被列出来。当该列为 NULL 时就要考虑当前的 SQL 是否需要优化了。
key 实际使用的索引
显示 MySQL 在查询中实际使用的索引,若没有使用索引,显示为 NULL。
查询中若使用了覆盖索引(覆盖索引:索引的数据覆盖了需要查询的所有数据),则该索引仅出现在 key 列表中
key_length 索引长度
显示 MySQL 实际决定使用的索引的长度。如果索引是 NULL,则长度为 NULL。如果不是 NULL,则为使用的索引的长度。所以通过此字段就可推断出使用了那个索引。
计算规则:
1、定长字段,int 占用 4 个字节,date 占用 3 个字节,char(n)
占用 n 个字符。
2、变长字段 varchar(n)
,则占用 n 个字符 + 两个字节。
3、不同的字符集,一个字符占用的字节数是不同的。Latin1 编码的,一个字符占用一个字节,gdk 编码的,一个字符占用两个字节,utf-8 编码的,一个字符占用三个字节。
4、对于所有的索引字段,如果设置为NULL,则还需要1个字节。
下面是索引长度 char()
、varchar()
索引长度的计算公式:
(Character Set:utf8mb4=4,utf8=3,gbk=2,latin1=1) * 列长度 + 1(允许null) + 2(变长列)
其他类型索引长度的计算公式: ex:
CREATE TABLE `student` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(128) NOT NULL DEFAULT '',
`age` int(11),
PRIMARY KEY (`id`),
UNIQUE KEY `idx` (`name`),
KEY `idx_age` (`age`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4;
name 索引长度为: 编码为 utf8mb4,列长为 128,不允许为 NULL,字段类型为 varchar(128)
。
所以:
key_length = 128 * 4 + 0 + 2 = 514;
age 索引长度:int 类型占 4 位,允许 null,索引长度为 5。
ref
表示上述表的连接匹配条件,即哪些列或常量被用于查找索引列上的值
rows
返回估算的结果集数目,并不是一个准确的值。
extra
extra 的信息非常丰富,常见的有:
- Using index 使用覆盖索引
- Using where 使用了用
where
子句来过滤结果集 - Using filesort 使用文件排序,使用非索引列进行排序时出现,非常消耗性能,尽量优化。
- Using temporary 使用了临时表